﻿using ExcelDataReader;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Z.Dapper.Plus;

namespace ReadExcelApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();


            string desc = " 1. 这是一个读取Excel到数据库的范例，使用的是ExcelDataReader组件，用Nuget获取。" + "\r\n" 
                        + " 2. 其他组件包括MySql.Data、Z.Dapper.Plus。";
            txtDescription.Text = desc;
                        
            this.dgvMain.Focus();
        }

        

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];
            //dgvMain.DataSource = dt;

            if (dt != null)
            { 
                List<CustomerModel> list = new List<CustomerModel>();

                for (int i = 0; i < dt.Rows.Count; i++)
                { 
                    CustomerModel model = new CustomerModel();

                    //string name = dt.Rows[i]["CustomerName"].ToString();

                    model.CustomerName = dt.Rows[i]["姓名"].ToString();
                    model.IdCardNumber = dt.Rows[i]["身份证"].ToString();
                    model.Sex = dt.Rows[i]["性别"].ToString();
                    model.Brithday = Convert.ToDateTime(dt.Rows[i]["生日"].ToString());
                    model.Age = Convert.ToInt32(dt.Rows[i]["年龄"].ToString());
                    model.NativePlace = dt.Rows[i]["籍贯"].ToString();

                    list.Add(model);
                }

                customerModelBindingSource.DataSource = list;
            }

        }

        DataTableCollection tableCollection;

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "All Files|*.*|Excel Workbook|*.xlsx|Excel 97-2003 Workbook |*.xls" };

            using(openFileDialog)
            {
                if(openFileDialog.ShowDialog() == DialogResult.OK) 
                { 
                    txtFilename.Text = openFileDialog.FileName;

                    // 判断文件是否被打开占用

                    try
                    {
                        using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                        {
                            using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                            {
                                DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                                {
                                    ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                                });

                                tableCollection = result.Tables;
                                cboSheet.Items.Clear();

                                foreach (DataTable table in tableCollection)
                                    cboSheet.Items.Add(table.TableName);    // Add sheet to combobox
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                }

            }
        }

        private void btnImportToDb_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                string connectionString = "server=localhost;port=3307;database=testdb;uid=root;pwd=root";
                DapperPlusManager.Entity<CustomerModel>().Table("Customer_Excel");

                List<CustomerModel> customers = customerModelBindingSource.DataSource as List<CustomerModel>;

                if (customers != null)
                { 
                    //using(IDbConnection db = new SqlConnection(connectionString))
                    using (IDbConnection db = new MySqlConnection(connectionString))
                    {
                        db.BulkInsert(customers);
                        //db.BulkInsertAsync(customers);
                    }
                }
                MessageBox.Show("导入完成！", "Message");

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            Cursor.Current = Cursors.Default;
        }
    }
}
